﻿using System.Collections.Generic;
using System.Data;

using agsXMPP.Net.Dns;

using Community.CsharpSqlite.SQLiteClient;

namespace VSTalk.Engine.Core.History
{
    public class HistoryDataProvider : IHistoryDataProvider
    {
        private SqliteConnection _connection;

        public void Init(SqliteConnection connection)
        {
            _connection = connection;
        }

        public void SaveMessages(IList<StoredMessage> messages)
        {
            _connection.Open();
            foreach (var storedMessage in messages)
            {
                var command = _connection.CreateCommand();
                command.CommandText = "insert into history (client_id, from_jid, to_jid, timestamp, message) values(@p1, @p2, @p3, @p4, @p5)";
                command.CommandType = CommandType.Text;
                command.Parameters.Add(new SqliteParameter("@p1", storedMessage.ClientId));
                command.Parameters.Add(new SqliteParameter("@p2", storedMessage.From));
                command.Parameters.Add(new SqliteParameter("@p3", storedMessage.To));
                command.Parameters.Add(new SqliteParameter("@p4", storedMessage.Date));
                command.Parameters.Add(new SqliteParameter("@p5", storedMessage.MessageXml));
                command.ExecuteNonQuery();
            }
            _connection.Close();
        }

        public IList<StoredMessage> ReadMessages(string clientId, string withJid)
        {
            _connection.Open();
            var command = _connection.CreateCommand();
            
            command.CommandText = @"
select * from history 
where client_id = @p1 and 
(from_jid = @p2 or to_jid = @p2)";

            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", clientId));
            command.Parameters.Add(new SqliteParameter("@p2", withJid));

            var reader = command.ExecuteReader();
            var result = new List<StoredMessage>();
            while (reader.Read())
            {
                var msg = new StoredMessage();
                msg.ClientId = reader.GetString(1);
                msg.From = reader.GetString(2);
                msg.To = reader.GetString(3);
                msg.Date = reader.GetInt64(4);
                msg.MessageXml = reader.GetString(5);
                result.Add(msg);
            }
            _connection.Close();
            return result;
        }

        public int MessageCount(string id, string withJid, long fromTicks, long toTicks)
        {
            _connection.Open();
            var command = _connection.CreateCommand();

            command.CommandText = @"
select count(*) from history 
where client_id = @p1 and (from_jid = @p2 or to_jid = @p2)
and @p3 < timestamp and timestamp <= @p4
";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", id));
            command.Parameters.Add(new SqliteParameter("@p2", withJid));
            command.Parameters.Add(new SqliteParameter("@p3", fromTicks));
            command.Parameters.Add(new SqliteParameter("@p4", toTicks));

            var reader = command.ExecuteReader();
            reader.Read();
            var result = reader.GetInt32(0);
            _connection.Close();
            return result;
        }

        public IList<StoredMessage> ReadMessages(string clientId, string withJid, long fromTicks, long toTicks)
        {
            _connection.Open();
            var command = _connection.CreateCommand();

            command.CommandText = @"
select * from history 
where client_id = @p1 and 
(from_jid = @p2 or to_jid = @p2) and
(@p3 < timestamp and timestamp <= @p4)
";

            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", clientId));
            command.Parameters.Add(new SqliteParameter("@p2", withJid));
            command.Parameters.Add(new SqliteParameter("@p3", fromTicks));
            command.Parameters.Add(new SqliteParameter("@p4", toTicks));

            var reader = command.ExecuteReader();
            var result = new List<StoredMessage>();
            while (reader.Read())
            {
                var msg = new StoredMessage();
                msg.ClientId = reader.GetString(1);
                msg.From = reader.GetString(2);
                msg.To = reader.GetString(3);
                msg.Date = reader.GetInt64(4);
                msg.MessageXml = reader.GetString(5);
                result.Add(msg);
            }
            _connection.Close();
            return result;
        }

        public IList<StoredMessage> ReadMessages(string clientId, string withJid, long fromTicks)
        {
            _connection.Open();
            var command = _connection.CreateCommand();

            command.CommandText = @"
select * from history 
where client_id = @p1 and 
(from_jid = @p2 or to_jid = @p2) and
@p3 < timestamp
";

            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", clientId));
            command.Parameters.Add(new SqliteParameter("@p2", withJid));
            command.Parameters.Add(new SqliteParameter("@p3", fromTicks));

            var reader = command.ExecuteReader();
            var result = new List<StoredMessage>();
            while (reader.Read())
            {
                var msg = new StoredMessage();
                msg.ClientId = reader.GetString(1);
                msg.From = reader.GetString(2);
                msg.To = reader.GetString(3);
                msg.Date = reader.GetInt64(4);
                msg.MessageXml = reader.GetString(5);
                result.Add(msg);
            }
            _connection.Close();
            return result;
        }

        public long LatestMessageDate(string clientId, string withJid)
        {
            _connection.Open();
            var command = _connection.CreateCommand();

            command.CommandText = @"
select ifnull(min(timestamp),-1) from history 
where client_id = @p1 and (from_jid = @p2 or to_jid = @p2)
";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", clientId));
            command.Parameters.Add(new SqliteParameter("@p2", withJid));

            var reader = command.ExecuteReader();
            reader.Read();
            var result = reader.GetInt64(0);
            _connection.Close();
            return result;
        }
    }
}